from pymysql import connect, cursors
from pymysql.err import OperationalError
import os
import configparser

# 读取db_config.ini
base_dir = str(os.path.dirname(os.path.dirname(__file__)))
base_dir = base_dir.replace('\\', '/')
file_path = base_dir + '/db_config.ini'

cf = configparser.ConfigParser()
cf.read(file_path)

host = cf.get('mysqlconf', 'host')
port = cf.get('mysqlconf', 'port')
user = cf.get('mysqlconf', 'user')
db = cf.get('mysqlconf', 'db_name')
password = cf.get('mysqlconf', 'password')


# 封装DB基本操作
class DBHelper:
    def __init__(self):
        # 建立连接
        try:
            self.conn = connect(host=host, user=user, password=password, db=db, charset='utf8mb4',
                                cursorclass=cursors.DictCursor)
        except OperationalError as e:
            print("MySQL Error %d: %s" % (e.args[0], e.args[1]))

    # 清空某张表的数据
    def clear_table(self, table_name):
        real_sql = 'delete from ' + table_name + ';'
        with self.conn.cursor() as cursor:
            cursor.execute('SET FOREIGN_KEY_CHECKS=0;')
            cursor.execute(real_sql)
        self.conn.commit()

    # 插入某表数据
    def insert_data(self, table_name, table_data):
        for key in table_data:
            table_data[key] = "'" + str(table_data[key]) + "'"
        key = ','.join(table_data.keys())
        value = ','.join(table_data.values())
        real_sql = 'insert into ' + table_name + '(' + key + ') values (' + value + ');'
        print(real_sql)
        with self.conn.cursor() as cursor:
            cursor.execute(real_sql)
        self.conn.commit()

    # 关闭数据库连接
    def close_conn(self):
        self.conn.close()


if __name__ == '__main__':
    db = DBHelper()
    table_name = 'sign_event'
    data = {'id': 12, 'name': '红米', 'limit': 2000, 'status': 1, 'address': '北京会展中心',
            'start_time': '2017-01-01 00:25:30'}
    db.insert_data(table_name, data)
    db.close_conn()
